create or replace function sys_func_reverse_geocode(coordinate text, semantic character varying) returns text
    language plpythonu
as
$$
import reverse_geocode

if __name__ == "__main__":
    try:
        coord_split = coordinate.split(',')
        coord = (float(coord_split[1]), float(coord_split[0]))

        if semantic == 'province':
            ret = reverse_geocode.get(coord)['city']
            sql = "select province from dataset._city_mapper_ where en = '%s'" % (ret)
            rs = plpy.execute(sql)
            ret = rs[0]['province']
        else:
            ret = reverse_geocode.get(coord)[semantic]
            if semantic == 'country' and (ret == 'Taiwan' or ret == 'Hong Kong' or ret == 'Macao'):
                ret = 'China'
    except:
        ret = None
    return ret
$$;

alter function sys_func_reverse_geocode(text, varchar) owner to gpadmin;

